package control;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.swing.JCheckBox;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

import model.Student;

public class StudentDAO {
	private final String className="com.mysql.jdbc.Driver";
	private final String url="jdbc:mysql://localhost:3307/ftico";
	private final String table="students";
	private Connection con;
	public StudentDAO(){
		try {
			Class.forName(className);
			con=DriverManager.getConnection(url,"root","conghv911");
			System.out.println("Connect success!");
		} catch (ClassNotFoundException e) {
			System.out.println("Class not found!");
		} catch (SQLException e) {
			System.out.println("Error connect!");
		}
	}
	public void showData(ResultSet rs){
		try {
			while(rs.next()){
				System.out.printf("%-10d %-20s %-10d %-20s %-20s\n",rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4),rs.getString(5));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public ResultSet getData(){
		ResultSet rs=null;
		String sqlCommand="select *from "+ table;
		Statement st;
		try {
			st=con.createStatement();
			rs=st.executeQuery(sqlCommand);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}
	public ResultSet getDataId(String id){
		ResultSet rs=null;
		String sqlCommand="select *from "+ table+ " where id= ?";
		PreparedStatement pst = null;
		try {
			pst=con.prepareStatement(sqlCommand);
			pst.setString(1, id);
			rs=pst.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
	}
	public void addStudent(Student student){ 
		String sql="insert into students(idStudent, nameStudent, ageStudent, sexStudent, homeTown) values(?, ?, ?, ?, ?)" ;
		try {
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, student.getId());
			ps.setString(2, student.getName());
			ps.setInt(3, student.getAge());
			ps.setString(4, student.getSex());
			ps.setString(5, student.getHomeTown());
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public void loadDB(JTable tb){
		String query = "select*from students";
		DefaultTableModel m=new DefaultTableModel();
		tb.setModel(m);
                m.addColumn("ID");m.addColumn("Name");m.addColumn("Sex");m.addColumn("Age");m.addColumn("HomeTown");
		try{ 
			Statement stmt= con.createStatement();
			ResultSet rs= stmt.executeQuery(query);
			while(rs.next()) {
				m.addRow(new String[]{rs.getString(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getString(5)});
//                                m.addRow(c);
			} 
			}catch(SQLException e) {
			e.printStackTrace();
			}
		
        
	}
        public void deleteDB(JTable tb){
            for(int i=0;i<tb.getSelectedRowCount();i++){
            	int k=tb.getSelectedRow();
                int tmp=Integer.parseInt(tb.getValueAt(k, 0).toString());
                String query = "delete from students where idStudent = "+tmp+"";
//                System.out.println(tmp);
                try{ 
                    Statement stmt= con.createStatement();
                    int rs= stmt.executeUpdate(query);
//                    ResultSet rs = stmt.executeUpdate(query);
                    }catch(SQLException e) {
                    e.printStackTrace();
                }
            }
            
        }
        
}
